package cn.fintecher.pangolin.service.repair.utils;

import cn.fintecher.pangolin.common.annotation.ExcelAnno;
import cn.fintecher.pangolin.common.exception.BadRequestException;
import cn.fintecher.pangolin.common.model.CaseInfoPropertyResponse;
import cn.fintecher.pangolin.common.model.RemarkModel;
import cn.fintecher.pangolin.common.utils.BeanUtils;
import cn.fintecher.pangolin.common.utils.SaxParseExcelUtil;
import cn.fintecher.pangolin.common.utils.ZWDateUtil;
import cn.fintecher.pangolin.entity.elastic.ImportExcelConfigItem;
import com.google.common.collect.Maps;
import com.google.gson.Gson;
import com.google.gson.JsonObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StopWatch;

import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;

import static cn.fintecher.pangolin.common.utils.ExportDataToExcelUtil.ROW_MEMORY;

/**
 * @Author:hanwannan
 * @Desc:
 * @Date:Create in 9:48 2018/9/1
 */
public class ImportExcelUntil {
    private static Logger logger = LoggerFactory.getLogger(ImportExcelUntil.class);

    /**
     * 拼装单个obj
     *
     * @param clazz
     * @param sheet
     * @return
     * @throws Exception
     */
    private static <T> T dataObj(Class<T> clazz, String template, Sheet sheet, int rowIndex, List<String> errorList) throws Exception {
        //json数据模板转换为map
        Gson gson = new Gson();
        HashMap templateMap = gson.fromJson(template, HashMap.class);

        //获取标题row
        Row header = sheet.getRow(0);
        //获取当前row
        Row row = sheet.getRow(rowIndex);

        //容器
        T t = clazz.newInstance();
        for (Field field : clazz.getDeclaredFields()) {
            System.out.println(field.getName());
        }

        //注意excel表格字段顺序要和obj字段顺序对齐 （如果有多余字段请另作特殊下标对应处理）
        Field field = null;
        String headTitle = null;
        List<RemarkModel> remarkMap = new ArrayList<>();
        for (int colIndex = 0; colIndex < row.getPhysicalNumberOfCells(); colIndex++) {
            headTitle = header.getCell(colIndex).getStringCellValue().trim();
            if (templateMap.get(headTitle) != null) {
                String fieldName = (String) templateMap.get(headTitle);
                field = t.getClass().getDeclaredField(fieldName);
                //打开实体中私有变量的权限
                field.setAccessible(true);
                field.set(t, getVal(field, row.getCell(colIndex), rowIndex, colIndex, errorList));
            } else {
                //无对应的数据字段
                RemarkModel remarkModel=new RemarkModel();
                remarkModel.setKey(headTitle);
                remarkModel.setValue(String.valueOf(getVal(field, row.getCell(colIndex), rowIndex, colIndex, errorList)));
                remarkMap.add(remarkModel);
            }
        }

        //设置导入日期
        field = t.getClass().getDeclaredField("importDate");
        field.setAccessible(true);
        field.set(t, new Date());
        field = t.getClass().getDeclaredField("operatorTime");
        field.setAccessible(true);
        field.set(t, new Date());
        field = t.getClass().getDeclaredField("remarkMap");
        field.setAccessible(true);
        field.set(t,remarkMap);
        return t;
    }

    public static <T> List<T> importExcel(Class<T> clazz, String template, InputStream inputStream, List<String> errorList) throws Exception {

        try {
            //装载流
            Workbook hw = WorkbookFactory.create(inputStream);
            //获取第一个sheet页
            Sheet sheet = hw.getSheetAt(0);
            //容器
            List<T> ret = new ArrayList<>();

            //遍历行 从下标第一行开始（去除标题）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    //装载obj
                    if (row.getCell(0) != null) {
                        ret.add(dataObj(clazz, template, sheet, i, errorList));
                    }
                }
            }
            return ret;
        }catch (Exception e){
            throw new Exception("file.is.illegal");
        }
    }

    public static String getJsonStr(Class clazz){
        JsonObject jsonObject = new JsonObject();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            //获取标记了ExcelAnno的注解字段
            if (field.isAnnotationPresent(ExcelAnno.class)) {
                ExcelAnno f = field.getAnnotation(ExcelAnno.class);
                jsonObject.addProperty(f.cellName(),field.getName());
            }
        }
        return jsonObject.toString();
    }


    public static List<CaseInfoPropertyResponse> getObjejctPro( List<Class<?>> objClassList){
        List<CaseInfoPropertyResponse> caseInfoPropertyResponseList=new ArrayList<>();
        for(Class<?> objClass : objClassList){
            //获取类中所有的字段
            Field[] fields = objClass.getDeclaredFields();
            for (Field field : fields) {
                //获取标记了ExcelAnno的注解字段
                if (field.isAnnotationPresent(ExcelAnno.class)) {
                    ExcelAnno f = field.getAnnotation(ExcelAnno.class);
                    CaseInfoPropertyResponse caseInfoPropertyResponse=new CaseInfoPropertyResponse();
                    caseInfoPropertyResponse.setAttribute(field.getName());
                    caseInfoPropertyResponse.setName(f.cellName());
                    caseInfoPropertyResponse.setPropertyType(f.fieldType().name());
                    caseInfoPropertyResponseList.add(caseInfoPropertyResponse);
                }
            }
        }

        return caseInfoPropertyResponseList;
    }

    /**
     * 处理val
     *
     * @param cell
     * @return
     */
    private static Object getVal(Field field, Cell cell, int rowIndex, int colIndex, List<String> errorList) {
        String cellValue = null;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR:
                cellValue = "";
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = String.valueOf(ZWDateUtil.fomratterDate(cell.getDateCellValue(), "yyyy/MM/dd"));
                } else {
                    DecimalFormat df = new DecimalFormat("#.#########");
                    cellValue = df.format(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_FORMULA:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = String.valueOf(ZWDateUtil.fomratterDate(cell.getDateCellValue(), "yyyy/MM/dd"));
                } else {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING:
                cellValue = StringUtils.trim(cell.getStringCellValue());
                break;
            default:
                break;
        }

        if (StringUtils.isEmpty(cellValue)) {
            return null;
        }
        Object obj = null;
        Class fieldDataType = field.getType();
        if (fieldDataType == String.class) {
            obj = cellValue;
        } else if (fieldDataType == Integer.class) {
            try {
                obj = Integer.parseInt(cellValue);
            } catch (NumberFormatException e) {
                logger.error(e.getMessage(), e);
                errorList.add(createErrorStr(cellValue, "数值", rowIndex, colIndex, 1));
            }
        } else if (fieldDataType == Double.class) {
            try {
                BigDecimal bigDecimal = new BigDecimal(cellValue.replaceAll(",", ""));
                obj = Math.abs(bigDecimal.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
            } catch (NumberFormatException e) {
                logger.error(e.getMessage(), e);
                errorList.add(createErrorStr(cellValue, "数值", rowIndex, colIndex, 1));
            }
        } else if (fieldDataType == Date.class) {
            try {
                if (cellValue.matches("\\d{4}/\\d{1,2}/\\d{1,2}")) {
                    obj = ZWDateUtil.getUtilDate(cellValue, "yyyy/MM/dd");
                } else if (cellValue.matches("\\d{4}-\\d{2}-\\d{2}")) {
                    obj = ZWDateUtil.getUtilDate(cellValue, "yyyy-MM-dd");
                } else if (cellValue.matches("^\\d{4}\\d{2}\\d{2}")) {
                    obj = ZWDateUtil.getUtilDate(cellValue, "yyyyMMdd");
                } else if (cellValue.matches("\\d{4}.\\d{1,2}.\\d{1,2}")) {
                    obj = ZWDateUtil.getUtilDate(cellValue, "yyyy.MM.dd");
                } else if (cellValue.matches("\\d{1,2}/\\d{1,2}/\\d{4}")) {
                    obj = ZWDateUtil.getUtilDate(cellValue, "MM/dd/yyyy");
                } else if (cellValue.matches("\\d{1,2}/\\d{1,2}/\\d{2}")) {
                    obj = ZWDateUtil.getUtilDate(cellValue, "M/dd/yy");
                } else {
                    obj = null;
                    logger.error("日期格式无法匹配: {}", cellValue);
                }
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
                errorList.add(createErrorStr(cellValue, "日期", rowIndex, colIndex, 1));
            }
        } else {
            obj = cellValue;
        }
        return obj;
    }

    /**
     * 拼装错误信息
     *
     * @param cellValue
     * @param remark
     * @param rowIndex
     * @param colIndex
     * @return
     */
    private static String createErrorStr(String cellValue, String remark, int rowIndex, int colIndex, int sheetNum) {
        return "第[".concat(String.valueOf(sheetNum)).concat("]sheet页的第[")
                .concat(String.valueOf(rowIndex)).concat("]行,第[")
                .concat(String.valueOf(colIndex)).concat("]列的值[")
                .concat(cellValue).concat("]转为[").concat(remark).concat("]");
    }

    public static void main(String[] args) {
        Gson gson = new Gson();
        Map map = Maps.newHashMap();
        map.put("colour", "red");
        map.put("weight", "10kg");
        String mapJson = gson.toJson(map);
        System.out.println(mapJson);
    }

    public static String getFields(Class<?> objClass) {
        JsonObject jsonObject = new JsonObject();
        //获取类中所有的字段
        Field[] fields = objClass.getDeclaredFields();
        for (Field field : fields) {
            //获取标记了ExcelAnno的注解字段
            if (field.isAnnotationPresent(ExcelAnno.class)) {
                ExcelAnno f = field.getAnnotation(ExcelAnno.class);
                jsonObject.addProperty(f.cellName(),field.getName());
            }
        }
        return jsonObject.toString();
    }

    /**
     *
     * @param titleNames 标题项
     * @param dataPros 属性值
     * @param filePath 文件路径+文件名称
     */
    public static void exportToExcel(List<?> hasData, List<?> repairData, String[] titleNames, String[] dataPros,String filePath,String sheetName) throws BadRequestException {

        FileOutputStream fos=null;
        SXSSFWorkbook wb=null;
        try {
            fos = new FileOutputStream(filePath);
            wb = new SXSSFWorkbook(ROW_MEMORY);
            CellStyle cellHeadStyle=headCellType(wb);
            CellStyle dataStyle=dataCellType(wb);
            //写入头信息
            Sheet sheet = null;
            if(Objects.nonNull(hasData) && !hasData.isEmpty()){
                sheet = wb.createSheet("已存在数据");
                ImportExcelUntil.exportToSheet(hasData, titleNames, dataPros,sheet,cellHeadStyle, dataStyle);
            }
            if(Objects.nonNull(repairData) && !repairData.isEmpty()){
                sheet = wb.createSheet("不存在数据");
                ImportExcelUntil.exportToSheet(repairData, titleNames, dataPros,sheet,cellHeadStyle, dataStyle);
            }
            wb.write(fos);
            fos.flush();
        }catch (Exception e){
            e.printStackTrace();
            throw  new BadRequestException(null, "exportToExcel", "导出数据失败");
        }finally {
            try {
                if(Objects.isNull(fos)){
                    fos.flush();
                    fos.close();
                }
                if(Objects.isNull(wb)){
                    wb.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            //手动清除list
            hasData.clear();
            repairData.clear();
        }
    }

    private static void exportToSheet(List<?> dataList, String[] titleNames, String[] dataPros,Sheet sheet,CellStyle cellHeadStyle,CellStyle dataStyle) throws Exception {

        Row row = sheet.createRow(0);
        for(int i=0;i<titleNames.length;i++){
            Cell cell = row.createCell(i);
            cell.setCellStyle(cellHeadStyle);
            cell.setCellValue(titleNames[i]);
            sheet.setColumnWidth(i, 7000);
        }
        for (int rowIndex=0;rowIndex<dataList.size();rowIndex++){
            Row dataRow = sheet.createRow(rowIndex+1);
            for (int colIndex=0;colIndex<dataPros.length;colIndex++){
                Cell  cell = dataRow.createCell(colIndex);
                cell.setCellStyle(dataStyle);
                Field field = dataList.get(rowIndex).getClass().getDeclaredField(dataPros[colIndex]);
                if(Objects.nonNull(field)) {
                    //设置对象的访问权限，保证对private的属性的访问
                    field.setAccessible(true);
                    if (Objects.equals(field.getType().getName(), "java.util.Date")) {
                        cell.setCellValue(Objects.nonNull(field.get(dataList.get(rowIndex))) ? ZWDateUtil.fomratterDate((Date) field.get(dataList.get(rowIndex)), "yyy-MM-dd HH:mm:ss") : null);
                    } else {
                        cell.setCellValue(Objects.nonNull(field.get(dataList.get(rowIndex))) ? field.get(dataList.get(rowIndex)).toString() : null);
                    }
                }
            }
        }
    }

    /**
     * 标题
     * @param wb
     * @return
     */
    private static CellStyle headCellType(SXSSFWorkbook wb){
        CellStyle cellHeadStyle = wb.createCellStyle();
        DataFormat  format = wb.createDataFormat();
        cellHeadStyle.setDataFormat(format.getFormat("@"));
        //边框
        cellHeadStyle.setBorderBottom(BorderStyle.THIN);
        cellHeadStyle.setBorderLeft(BorderStyle.THIN);
        cellHeadStyle.setBorderRight(BorderStyle.THIN);
        cellHeadStyle.setBorderTop(BorderStyle.THIN);
        cellHeadStyle.setAlignment(HorizontalAlignment.CENTER);
        //自动换行
        cellHeadStyle.setWrapText(false);
        //字体设置
        Font f = wb.createFont();
        f.setFontHeightInPoints((short) 12);
        f.setBold(true);
        cellHeadStyle.setFont(f);
        return cellHeadStyle;
    }

    /**
     * 数据
     * @param wb
     * @return
     */
    private static CellStyle dataCellType(SXSSFWorkbook wb){
        CellStyle dataCellType = wb.createCellStyle();
        DataFormat  format = wb.createDataFormat();
        dataCellType.setDataFormat(format.getFormat("@"));
        //边框
        dataCellType.setBorderBottom(BorderStyle.THIN);
        dataCellType.setBorderLeft(BorderStyle.THIN);
        dataCellType.setBorderRight(BorderStyle.THIN);
        dataCellType.setBorderTop(BorderStyle.THIN);
        dataCellType.setAlignment(HorizontalAlignment.CENTER);
        //自动换行
        dataCellType.setWrapText(true);
        //字体设置
        Font f = wb.createFont();
        f.setFontHeightInPoints((short) 9);
        f.setBold(false);
        dataCellType.setFont(f);
        return dataCellType;
    }

    /***
     * Excel解析数据
     * @param inputStream
     * @return
     */
    public static List<Map<String, String>> parseExcelAssistManagementData(InputStream inputStream) {

        Map<Integer, List<Map<String, String>>> dataMap = null;
        List<Map<String, String>> sheetDataList = null;
        try {
            logger.info("解析数据文件开始........");
            StopWatch watch = new StopWatch();
            watch.start();
            //数据信息
            dataMap = SaxParseExcelUtil.parseExcel(inputStream, 1, 1, -1, 1);
            watch.stop();
            logger.info("解析数据文件结束，耗时：{}", watch.getTotalTimeMillis());
            //获取第一个Sheet的数据
            sheetDataList = dataMap.get(1);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return sheetDataList;
    }

    /**
     * 将Excel中的表头转化为MAP
     *
     * @param heardList
     * @return
     */
    public static Map<String, ImportExcelConfigItem> excelConfigItemToMap(List<Map<String, String>> heardList, List<Class<?>> objClassList) {

        List<CaseInfoPropertyResponse> responses = getObjejctPro(objClassList);
        Map<String, ImportExcelConfigItem> itemMap = new HashMap<>();
        if (heardList.size() > 0) {
            Map<String, String> map = heardList.get(0);
            for (CaseInfoPropertyResponse response : responses) {
                ImportExcelConfigItem item = new ImportExcelConfigItem();
                for (Map.Entry<String, String> filed : map.entrySet()) {
                    if (response.getName().equals(filed.getValue())) {
                        BeanUtils.copyProperties(response, item);
                        item.setCol(filed.getKey());
                        itemMap.put(item.getCol(), item);
                    }
                }
            }
        }
        return itemMap;
    }
    /**
     * 解析每行数据转为对应的实体对象
     */
    public static void parseCellMap(Object obj, String cellValue, ImportExcelConfigItem importExcelConfigItem, List<String> errorList, long rowIndex, int sheetIndex) {
        try {
            Field field = obj.getClass().getDeclaredField(importExcelConfigItem.getAttribute());
            //打开实体中私有变量的权限
            field.setAccessible(true);
            Object object = fomatValue(field, cellValue, errorList, rowIndex, importExcelConfigItem.getCol(), sheetIndex);
            field.set(obj, object);

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
    }

    /**
     * 格式化数据
     *
     * @param field
     * @param cellValue
     * @return
     */
    private static Object fomatValue(Field field, String cellValue, List<String> errorList, long rowIndex, String colStr, int sheetNum) {
        if (org.apache.commons.lang.StringUtils.isEmpty(cellValue)) {
            return null;
        }
        Object obj = null;
        ExcelAnno.FieldDataType fieldDataType = field.getAnnotation(ExcelAnno.class).fieldDataType();
        switch (fieldDataType) {
            case STRING:
                obj = cellValue;
                break;
            case INTEGER:
                try {
                    obj = Integer.parseInt(cellValue);
                } catch (NumberFormatException e) {
                    logger.error(e.getMessage(), e);
                }
                break;
            case DOUBLE:
                try {
                    BigDecimal bigDecimal = new BigDecimal(cellValue.replaceAll(",", ""));
                    obj = Math.abs(bigDecimal.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
                } catch (NumberFormatException e) {
                    logger.error(e.getMessage(), e);
                }
                break;
            case DATE:
                try {
                    if (cellValue.matches("\\d{4}/\\d{1,2}/\\d{1,2}")) {
                        obj = ZWDateUtil.getUtilDate(cellValue, "yyyy/MM/dd");
                    } else if (cellValue.matches("\\d{4}-\\d{2}-\\d{2}")) {
                        obj = ZWDateUtil.getUtilDate(cellValue, "yyyy-MM-dd");
                    } else if (cellValue.matches("^\\d{4}\\d{2}\\d{2}")) {
                        obj = ZWDateUtil.getUtilDate(cellValue, "yyyyMMdd");
                    } else if (cellValue.matches("\\d{4}.\\d{1,2}.\\d{1,2}")) {
                        obj = ZWDateUtil.getUtilDate(cellValue, "yyyy.MM.dd");
                    } else if (cellValue.matches("\\d{1,2}/\\d{1,2}/\\d{4}")) {
                        obj = ZWDateUtil.getUtilDate(cellValue, "MM/dd/yyyy");
                    } else if (cellValue.matches("\\d{1,2}/\\d{1,2}/\\d{2}")) {
                        obj = ZWDateUtil.getUtilDate(cellValue, "M/dd/yy");
                    } else {
                        obj = null;
                    }
                    break;
                } catch (Exception e) {
                    logger.error(e.getMessage(), e);
                }
            default:
                obj = cellValue;
                break;
        }
        return obj;
    }

}
